import numpy as np
import pandas as pd


def clean_num(x):
    try:
        x = float(str(x).replace('℃', ''))
    except:
        return np.nan
    return x


def mode(x):
    return x.mode()[0]


# 要求：清洗完后数据的城市顺序需和原文件中的城市顺序保持一致。
def deal_day_data():
    # TODO 清洗日表数据
    # 1.获取数据
    data = pd.read_csv('../data/day.csv')
    pd.set_option('display.max_columns', None)
    # 2.构建每个城市每一天的日期，为后续填充做好准备
    cits = data['city'].unique()
    date = list(pd.date_range('2011-1-1', '2022-4-30', freq='d'))
    df_all_date = pd.DataFrame()
    for city in cits:
        item = pd.DataFrame(date, columns=['date'])
        item['city'] = city
        df_all_date = pd.concat([df_all_date, item], axis=0)
    df_all_date['date'] = pd.to_datetime(df_all_date['date'])
    # 3.数据切分
    data[['date', 'week']] = data['date_week'].str.split(' ', 1, expand=True)
    data[['wind_direction', 'wind_level']] = data['wind'].str.split(' ', 1, expand=True)
    del data['date_week']
    del data['wind']
    # 3.类型转换
    data['hightest_tem'] = data['hightest_tem'].apply(clean_num)
    data['lowest_tem'] = data['lowest_tem'].apply(clean_num)
    data['date'] = pd.to_datetime(data['date'])
    # 4.数据清理
    # 去重
    data.drop_duplicates(subset=['city', 'date'], keep='first', inplace=True)
    # 补差
    data = pd.merge(df_all_date, data, left_on=['city', 'date'], right_on=['city', 'date'], how='left')
    group = data.groupby(['city', data['date'].dt.strftime('%m-%d')])
    data['hightest_tem'] = group['hightest_tem'].transform('mean')
    data['lowest_tem'] = group['lowest_tem'].transform('mean')
    data['weather'] = group['weather'].transform(mode)
    data['wind_direction'] = group['wind_direction'].transform(mode)
    data['wind_level'] = group['wind_level'].transform(mode)
    data['week'] = data['date'].dt.weekday.map({0: '星期一', 1: '星期二', 2: '星期三', 3: '星期四', 4: '星期五', 5: '星期六', 6: '星期日', })
    data['hightest_tem'] = data['hightest_tem'].round(2)
    data['lowest_tem'] = data['lowest_tem'].round(2)
    # 5.排序
    data = data.sort_values(['city', 'date'], ascending=[False, False])
    data['id'] = [x for x in range(1, len(data) + 1)]
    new_order = ['id', 'city', 'date', 'week', 'hightest_tem', 'lowest_tem', 'weather', 'wind_direction', 'wind_level']
    data = data[new_order]
    print(data.info())
    print(data.describe())
    print(data.head())
    data.to_csv('../data/cleaned_data/clean_day.csv', index=False)
    pass


if __name__ == '__main__':
    deal_day_data()
